import akshare as ak
import pandas as pd
import numpy as np
import matplotlib
from sqlalchemy import create_engine
from decouple import config
import time
from datetime import datetime, timedelta


class StockCollector:
    def __init__(self):
        self.__init_db__()

    def __init_db__(self):
        self.host = config("MYSQL_HOST")
        self.port = config("MYSQL_PORT")
        self.user = config("MYSQL_USER")
        self.password = config("MYSQL_PASSWORD")
        try:
            self.engine = create_engine('mysql+pymysql://{username}:{password}@{host}:{port}/stock?charset=utf8'
                                        .format(username=self.user, password=self.password, host=self.host, port=self.port))
        except:
            print("something wrong!")

    def query_current_years_by_fund_id(self):
        # 指定字段映射关系
        column_mapping = {
            'fund_id': 'fund_id',
            '日期': 'trade_date',
            '开盘': 'open_price',
            '收盘': 'closed_price',
            '最高': 'highest_price',
            '最低': 'minimum_price',
            '成交量': 'trade_volume',
            '成交额': 'volume_business',
            '振幅': 'amplitude',
            '涨跌幅': 'price_limit',
            '涨跌额': 'price_range',
            '换手率': 'turnover_rate',

        }
        dataset = pd.DataFrame(columns=["trade_date", "open_price",
                                        "closed_price", "highest_price", "minimum_price",
                                        "trade_volume", "volume_business", "amplitude",
                                        "price_limit", "price_range", "turnover_rate", "fund_id"])

        etf = ak.fund_etf_spot_em()
        for index, row in etf.iterrows():
            # print(index, row)
            print(row.get("代码"))
            fund_hist = None
            seven_days_ago = datetime.now() - timedelta(days=7)

            try:
                fund_hist = ak.fund_etf_hist_em(symbol=row.get("代码"), start_date=seven_days_ago.strftime('%Y%m%d'), adjust="qfq")
            except:
                time.sleep(5)
                fund_hist = ak.fund_etf_hist_em(symbol=row.get("代码"), start_date=seven_days_ago.strftime('%Y%m%d'), adjust="qfq")

            fund_hist['fund_id'] = row.get("代码")
            fund_hist.rename(columns=column_mapping, inplace=True)

            # 合并数据
            dataset = dataset._append(fund_hist, ignore_index=True)

        # 保存MySQL
        self.save_to_mysql(dataset)

    def save_to_mysql(self, data: pd.DataFrame):
        data.to_sql("etf_hist", con=self.engine, if_exists='replace', index=False)


if __name__ == '__main__':
    stock_collector = StockCollector()
    stock_collector.query_current_years_by_fund_id()
